package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Cms_topic10 {
	//插入
		@Test
		public void insert() {
			Connection conn=null;
			//准备数据库用户名，密码，url,driver
			String username="root";//用户名
			String password="root";//密码
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			try {
				//加载到驱动类
				Class.forName(driver);
				//获取连接
				conn=DriverManager.getConnection(url,password,username);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备sql命令
			String sql="insert into Cms_topic(id,category_id,name,create_time,start_time,end_time,attend_count,attention_count,read_count,award_name)values(8,2,'ere','2017-04-05','2017-04-05','2017-09-06',3,4,5,'yi')";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement对象st
				 st=conn.createStatement();
				//执行sql命令
				int row =st.executeUpdate(sql);
				System.out.println(row>0?"添加成功":"添加失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		//修改
		@Test
		public void update() {
			Connection conn=null;
			//准备数据库用户名，密码，url,driver
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			try {
				Class.forName(driver);
				//连接数据库
				conn=DriverManager.getConnection(url,password,username);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="update Cms_topic set category_id=4 where id=2";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.statement对象st
				st=conn.createStatement();
				//执行sql命令
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"修改成功":"修改失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}
			finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		} 
		@Test
		public void delet() {
			Connection connection=null;
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEncoding=utf-8";
			try {
				//加载驱动类到虚拟机
				Class.forName(driver);
				connection =DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="delete from Cms_topic where id=3";
			Statement statement=null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement对象st
				statement=connection.createStatement();
				int row=statement.executeUpdate(sql);
				System.out.println(row>0?"删除成功":"删除失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (statement!=null) {
					try {
						statement.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (connection!=null) {
					try {
						connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		@Test 
		public void stelet() {
			Connection con=null;
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode+true&characterEncoding=utf-8";
			try {
				//加载到虚拟机中
				Class.forName(driver);
				//获取数据库连接java.sql.connection
				con=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="select * from Cms_topic";
			Statement st=null;
			ResultSet rs = null;
			try {
				st=con.createStatement();
			     rs=st.executeQuery(sql);
				//处理查询结果集
				while(rs.next()) {
					//rs.getInt(columnIndex)：根据索引获取列的值，强烈不推荐
					//获取id列的值 id,category_id,name,create_time,start_time,end_time,attend_count,attention_count,read_count,award_name
					//System.out.println(rs.getInt(1));
					//rs.getInt(columnLabel)：columnLabel列名或列的别名，推荐使用
					System.out.println(rs.getInt("id"));
					System.out.println(rs.getInt("category_id"));
					System.out.println(rs.getObject("name"));
					System.out.println(rs.getObject("create_time"));
					System.out.println(rs.getObject("start_time"));
					System.out.println(rs.getObject("end_time"));
					System.out.println(rs.getObject("attend_count"));
					System.out.println(rs.getObject("attention_count"));
					System.out.println(rs.getObject("read_count"));
					System.out.println(rs.getObject("award_name"));
					//获取username列
					//System.out.println(rs.getString("username"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (con!=null) {
					try {
						con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (rs!=null) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}

}
